﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace SWDataDB.DAL
{
    public class ExcelReader
    {
        #region 读取钻孔图片的excel表

        /// <summary>
        /// 通过文件路径，读取钻孔图片的excel信息
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public DataSet ConvertExcelToDataTable(string path)
        {
            HSSFWorkbook hssfworkbook;
            DataSet dataSet1;
            dataSet1 = new DataSet();
            //read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.
            //book1.xls is an Excel-2007-generated file, so some new unknown BIFF records are added. 
            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }

            ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            DataTable dt = new DataTable();
            /*            int nR = sheet.*/
            for (int m = 0; m < 8; m++)
            {
                dt.Columns.Add(Convert.ToChar(((int)'A') + m).ToString());
            }

            int i = 0;
            int j = 0;
            int index = 1;    // 拼图片名称要用

            while (rows.MoveNext())
            {
                IRow row = (HSSFRow)rows.Current;
                DataRow dr = dt.NewRow();

                //ICell cell;
                HSSFCell cell;

                //第一行“127.辽宁抚顺红透山铜锌矿ZK2-1”
                if (i == 0)
                {
                    cell = row.GetCell(0) as HSSFCell;

                    if (cell.ToString() == "")
                    {
                        dr[0] = null;
                    }

                    else
                    {
                        dr[0] = cell.ToString();
                    }
                }

                //第二行
                //档号	矿区名称	 钻孔名称	岩心长度（米）	起始井段	 终止井段	总回次数
                else if (i == 1)
                {
                    for (j = 0; j < row.LastCellNum; j++)
                    {
                        cell = row.GetCell(j) as HSSFCell;

                        if (cell.ToString() == "")
                        {
                            dr[j] = null;
                        }

                        else
                        {
                            dr[j] = cell.ToString();
                        }
                    }
                }

                //第三行
                else if (i == 2)
                {
                    for (j = 0; j < row.LastCellNum; j++)
                    {
                        cell = row.GetCell(j) as HSSFCell;

                        if (cell.ToString() == "")
                        {
                            dr[j] = null;
                        }

                        else
                        {
                            dr[j] = cell.ToString();
                        }
                    }
                }

                //第四行
                else if (i == 3)
                {
                    cell = row.GetCell(0) as HSSFCell;

                    if (cell.ToString() == "")
                    {
                        dr[0] = null;
                    }

                    else
                    {
                        dr[0] = cell.ToString();
                    }
                }

                //第五行
                else if (i == 4)
                {

                }

                //第六行
                else if (i == 5)
                {
                }

                //第七行开始循环
                else
                {
                    int picNUM = 0;
                    int HC = 0;

                    for (j = 0; j < row.LastCellNum; j++)
                    {
                        cell = row.GetCell(j) as HSSFCell;

                        //回次
                        if (j == 0)
                        {
                            if (cell.ToString() == "")
                            {
                                dr[j] = dt.Rows[i - 1][0];

                                HC = int.Parse(dt.Rows[i - 1][0].ToString());
                            }

                            else
                            {
                                dr[j] = cell.ToString();
                                if (cell.CellType == 0)
                                {
                                    HC = int.Parse(cell.ToString());
                                }
                            }
                        }

                        //第二列起始深度
                        else if (cell.ToString() == "" && j == 1)
                        {
                            dr[j] = dt.Rows[i - 1][1];
                        }

                        //第三列终止深度
                        else if (cell.ToString() == "" && j == 2)
                        {
                            dr[j] = dt.Rows[i - 1][2];
                        }

                        //平扫图片数量
                        else if (j == 3)
                        {
                            if (cell.ToString() == "")
                            {
                                dr[j] = dt.Rows[i - 1][3];
                                picNUM = int.Parse(dt.Rows[i - 1][3].ToString());
                            }

                            else
                            {
                                dr[j] = cell.ToString();
                                picNUM = int.Parse(cell.ToString());
                            }

                        }

                        //图片文件名
                        else if (j == 4)
                        {
                            string picName;
                            picName = "HC" + HC.ToString() + "-" + index.ToString();

                            //dr[j] = cell.ToString();
                            dr[j] = picName;
                            index++;
                            if (index > picNUM)
                            {
                                index = 1;
                            }
                        }

                        //剩余
                        else
                        {
                            if (cell == null)
                            {
                                dr[j] = null;
                            }

                            else
                            {
                                dr[j] = cell.ToString();
                            }
                        }
                    }
                }

                dt.Rows.Add(dr);
                i++;
            }

            dataSet1.Tables.Add(dt);

            return dataSet1;
        }

        /// <summary>
        /// 通过文件路径，读取标本图片的excel信息
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public DataSet ConvertBBExcelToDataTable(string path)
        {
            HSSFWorkbook hssfworkbook;
            DataSet dataSet1;
            dataSet1 = new DataSet();
            //read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.
            //book1.xls is an Excel-2007-generated file, so some new unknown BIFF records are added. 
            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }

            ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            DataTable dt = new DataTable();
            /*            int nR = sheet.*/
            for (int m = 0; m < 7; m++)
            {
                dt.Columns.Add(Convert.ToChar(((int)'A') + m).ToString());
            }

            int i = 0;
            int j = 0;
            int index = 1;    // 拼图片名称要用

            while (rows.MoveNext())
            {
                IRow row = (HSSFRow)rows.Current;
                DataRow dr = dt.NewRow();

                //ICell cell;
                HSSFCell cell;

                //第一行“辽宁抚顺红透山矿区典型标本采样清单”
                if (i == 0)
                {
                    cell = row.GetCell(0) as HSSFCell;

                    if (cell.ToString() == "")
                    {
                        dr[0] = null;
                    }

                    else
                    {
                        dr[0] = cell.ToString();
                    }
                }

                //档号
                else if (i == 2)
                {
                    cell = row.GetCell(0) as HSSFCell;

                    if (cell.ToString() == "")
                    {
                        dr[0] = null;
                    }

                    else
                    {
                        dr[0] = cell.ToString();
                    }
                }

                //第三行
                //采集人：韩建        采集时间：2007年11月9日
                else if (i == 3)
                {
                    cell = row.GetCell(0) as HSSFCell;

                    if (cell.ToString() == "")
                    {
                        dr[0] = null;
                    }

                    else
                    {
                        dr[0] = cell.ToString();
                    }

                    cell = row.GetCell(5) as HSSFCell;



                    if (cell.ToString() == "")
                    {
                        dr[5] = null;
                    }

                    else
                    {
                        dr[5] = cell.ToString();
                    }
                }

                //第四行
                //序号	矿  山  名  称	标本编号	岩 石 名 称	岩 石 类 型	采  样  位  置	地质特征描述
                else if (i == 4)
                {
                    for (j = 0; j < row.LastCellNum; j++)
                    {
                        cell = row.GetCell(j) as HSSFCell;

                        if (cell.ToString() == "")
                        {
                            dr[j] = null;
                        }

                        else
                        {
                            dr[j] = cell.ToString();
                        }
                    }
                }


                ////第五行 （开始循环）
                else
                {
                    int picNUM = 0;
                    int HC = 0;

                    for (j = 0; j < row.LastCellNum; j++)
                    {
                        cell = row.GetCell(j) as HSSFCell;

                        //序号
                        if (j == 0)
                        {
                            //excel表中没写序号的话  不在这里处理 不影响
                            if (cell.ToString() == "")
                            {
                                dr[j] = null;
                            }

                            else
                            {
                                dr[j] = cell.ToString();
                            }
                        }

                        //第二列矿  山  名  称
                        else if (j == 1)
                        {
                            cell = row.GetCell(j) as HSSFCell;

                            if (cell.ToString() == "")
                            {
                                dr[j] = null;
                            }

                            else
                            {
                                dr[j] = cell.ToString();
                            }
                        }

                        //第三列标本编号
                        else if (j == 2)
                        {
                            cell = row.GetCell(j) as HSSFCell;

                            if (cell.ToString() == "")
                            {
                                dr[j] = null;
                            }

                            else
                            {
                                dr[j] = cell.ToString();
                            }
                        }

                        //第四列岩 石 名 称
                        else if (j == 3)
                        {
                            cell = row.GetCell(j) as HSSFCell;

                            if (cell.ToString() == "")
                            {
                                dr[j] = null;
                            }

                            else
                            {
                                dr[j] = cell.ToString();
                            }
                        }

                        //岩 石 类 型
                        else if (j == 4)
                        {
                            cell = row.GetCell(j) as HSSFCell;

                            if (cell.ToString() == "")
                            {
                                dr[j] = null;
                            }

                            else
                            {
                                dr[j] = cell.ToString();
                            }
                        }

                        //采  样  位  置
                        else if (j == 5)
                        {
                            cell = row.GetCell(j) as HSSFCell;

                            if (cell.ToString() == "")
                            {
                                dr[j] = null;
                            }

                            else
                            {
                                dr[j] = cell.ToString();
                            }
                        }

                        //地质特征描述
                        else
                        {
                            cell = row.GetCell(j) as HSSFCell;

                            if (cell.ToString() == "")
                            {
                                dr[j] = null;
                            }

                            else
                            {
                                dr[j] = cell.ToString();
                            }
                        }
                    }
                }

                dt.Rows.Add(dr);
                i++;
            }

            dataSet1.Tables.Add(dt);

            return dataSet1;
        }
        #endregion
    }
}
